package com.guanghua.brick.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class OracleDefaultSQLFilterSection implements ISQLFilterSection {
	
	private static final Log logger = LogFactory.getLog(OracleDefaultSQLFilterSection.class);
	
	public String onFilterSection(String conn, String dbProperty,
			String operator, String valueProperty,
			Map<String, Object> queryFilter, List<Object> param) {
		
		//检查传入的参数
		if (conn == null || conn.length() == 0 ||
			dbProperty == null || dbProperty.length() == 0 ||
			operator == null || operator.length() == 0 ||
			valueProperty == null || valueProperty.length() == 0 ||
			queryFilter == null || param == null) {
			logger.warn("filter section param wrong, please check flowing list");
			logger.warn("conn param:" + conn);
			logger.warn("dbProperty param:" + dbProperty);
			logger.warn("operator param:" + operator);
			logger.warn("valueProperty param:" + valueProperty);
			logger.warn("queryFilter param:" + queryFilter);
			logger.warn("paramList param:" + param);
			return "";
		}
		
		//如果没有该valuePropery说明没有输入该查询条件，那么返回“”
		if (!queryFilter.containsKey(valueProperty)) return "";
		
		
		StringBuffer ret = new StringBuffer();
		List<Object> paramList = new ArrayList<Object>();
		
		//获取输入的查询条件
		Object value = queryFilter.get(valueProperty);
		
		//如果value是null或者value是大写的NULL，那么查空字段
		if (value == null || "NULL".equals(value) || "".equals(value)) {
			doAppend(ret, conn, dbProperty, "is null", paramList);
		} else if ("!NULL".equals(value)) {
			doAppend(ret, conn, dbProperty, "is not null", paramList);
			
		//以下是不需要判断value的 filter操作	
		} else {
		
			//等于的处理
			if ("=".equals(operator) || "eq".equals(operator) || "equal".equals(operator)) {
				if (value instanceof Object[]) {
					doAppend(ret, conn, dbProperty, dealIn((Object[])value, paramList), paramList);
				} else if (value instanceof String || value instanceof Integer || value instanceof Long) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "= ?", paramList);
				}
			
			//多选的处理
			} else if ("in".equals(operator)) {
				if (value instanceof String) {
					String[] tmpArr = (String[])value.toString().split(",");
					doAppend(ret, conn, dbProperty, dealIn(tmpArr, paramList), paramList);
				} else if (value instanceof String[]) {
					String[] tmpArr = (String[])value;
					doAppend(ret, conn, dbProperty, dealIn(tmpArr, paramList), paramList);
				}
			
			} else if ("nin".equals(operator) || "notin".equals(operator)) {
				if (value instanceof String) {
					doAppend(ret, conn, dbProperty, dealIn(value.toString().split(","), paramList, "not in"), paramList);
				} else if (value instanceof String[]) {
					doAppend(ret, conn, dbProperty, dealIn((String[])value, paramList, "not in"), paramList);
				}
			
			//datetime 等于
			} else if ("dteq".equals(operator) || "datetimeequal".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "= to_date(?, 'yyyy-mm-dd hh24:mi:ss')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "= ?", paramList);
				}
			//date等于
			} else if ("deq".equals(operator) || "dateequal".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "= to_date(?, 'yyyy-mm-dd')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "= ?", paramList);
				}
				
			//模糊的处理
			} else if ("like".equals(operator)) {
				if (value instanceof String) {
					paramList.add("%"+value+"%");
					doAppend(ret, conn, dbProperty, "like ?", paramList);
				}
			} else if ("rlike".equals(operator) || "rightlike".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value+"%");
					doAppend(ret, conn, dbProperty, "like ?", paramList);
				}
			} else if ("llike".equals(operator) || "leftlike".equals(operator)) {
				if (value instanceof String) {
					paramList.add("%"+value);
					doAppend(ret, conn, dbProperty, "like ?", paramList);
				}
			} else if ("nlike".equals(operator) || "nonelike".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "like ?", paramList);
				}
			} else if ("notlike".equals(operator)) {
				if (value instanceof String) {
					paramList.add("%"+value+"%");
					doAppend(ret, conn, dbProperty, "not like ?", paramList);
				}
			
			//一般不等
			} else if (">".equals(operator) || ">=".equals(operator) || "<".equals(operator) || "<=".equals(operator) || "<>".equals(operator)) {
				if (value instanceof String || value instanceof Integer || value instanceof Long) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, operator + " ?", paramList);
				}
			//date 不等
			} else if ("dlt".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "< to_date(?, 'yyyy-mm-dd')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "< ?", paramList);
				}
			} else if ("dlteq".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "<= to_date(?, 'yyyy-mm-dd')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "<= ?", paramList);
				}
			} else if ("dgt".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "> to_date(?, 'yyyy-mm-dd')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "> ?", paramList);
				}
			} else if ("dgteq".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, ">= to_date(?, 'yyyy-mm-dd')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, ">= ?", paramList);
				}
			} else if ("dneq".equals(operator)) {
				if (value instanceof String) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "<> to_date(?, 'yyyy-mm-dd')", paramList);
				} else if (value instanceof Date) {
					paramList.add(value);
					doAppend(ret, conn, dbProperty, "<> ?", paramList);
				}
			}
		}
		
		logger.trace("parse filter seciton result:" + ret);
		logger.trace("parse filter seciton param:" + paramList);
		
		param.addAll(paramList);
		return ret.toString();
	}
	
	private String dealIn(Object[] value, List<Object> paramList) {
		return dealIn(value, paramList, "in");
	}
	
	private String dealIn(Object[] value, List<Object> paramList, String in) {
		StringBuffer sb = new StringBuffer(in);
		sb.append(" (");
		boolean hasNull = false;
		for (int i = 0; i < value.length ; i ++) {
			if (value[i] == null || "NULL".equals(value[i]) || "".equals(value[i])) {
				hasNull = true;
			} else {
				paramList.add(value[i]);
				sb.append("?, ");
			}
		}
		sb.setLength(sb.length()-2);
		sb.append(")");
		
		//如果有空值，那么在最后一个字符加一个空格，表示，这样doAppend的时候好做判断
		if (hasNull) sb.append(" ");
		return sb.toString();
	}
	
	private void doAppend (StringBuffer sb, String conn, String dbProperty, String other, List<Object> paramList) {
		//最后一个字符是空格，表示需要查询null
		boolean hasNull = (other.charAt(other.length()-1) == ' ');
		String[] dps = dbProperty.split(":");
		
		if (dps.length == 1) {
			if (hasNull)
				sb.append(conn)
				.append(" (")
				.append(dbProperty)
				.append(" ")
				.append(other)
				.append(" or ")
				.append(dbProperty)
				.append(" is null)");
			else
				sb.append(conn)
				.append(" ")
				.append(dbProperty)
				.append(" ")
				.append(other);
		} else {
			sb.append(conn).append(" (");
			List<Object> temp = new ArrayList<Object>();
			temp.addAll(paramList);
			for (int i = 0; i < dps.length ; i ++) {
				sb.append(dps[i]).append(" ").append(other).append(" or ");
				if (i != 0) paramList.addAll(temp);
				if (hasNull) sb.append(dps[i]).append(" is null or ");
			}
			sb.setLength(sb.length()-4);
			sb.append(")");
		}
	}
	
	public static void main (String[] a) {
		OracleDefaultSQLFilterSection odsfs = new OracleDefaultSQLFilterSection();
		Map<String, Object> queryFilter = new HashMap<String, Object>();
		queryFilter.put("name", "1,2,3,4,NULL,6");
		List<Object> paramList = new ArrayList<Object>();
		odsfs.onFilterSection("and", "studen.name:no:id", "in", "name", queryFilter, paramList);
	}

}
